﻿Imports System.Data.SqlClient

Public Class frmRetail

    Sub CheckNumbers()
        Try
            Me.Cursor = Cursors.WaitCursor

            For Each row As DataGridViewRow In Me.GridRetail.Rows
                row.Cells(Me.DGColBatchNo.Index).Value = row.Index + 1
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillInsurCompanies()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim InsurCompanies As ArrayList = GetInsurCompaniesList()

            Me.CombInsurComp.Items.Clear()

            For Each InsurComp As String In InsurCompanies
                Me.CombInsurComp.Items.Add(InsurComp)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmRetail_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
        FillInsurCompanies()

        Me.GridRetail.RowCount = 5

    End Sub

    Sub Clear()
        Me.GridRetail.Rows.Clear()
        Me.CombInsurComp.SelectedIndex = -1
    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        Clear()
    End Sub

    Private Sub GridRetail_RowsAdded(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewRowsAddedEventArgs) Handles GridRetail.RowsAdded
        CheckNumbers()
    End Sub

    Private Sub GridRetail_RowsRemoved(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewRowsRemovedEventArgs) Handles GridRetail.RowsRemoved
        CheckNumbers()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.ErrProv.Clear()
        If Me.GridRetail.Rows.Count = 0 Then
            Exit Sub
        ElseIf Me.CombInsurComp.SelectedIndex = -1 Then
            ErrProv.SetError(Me.CombInsurComp, "Please complete the details")
            Exit Sub
        Else
            Try
                Me.Cursor = Cursors.WaitCursor
                'Validate rows
                Dim Valid As Boolean = True
                For Each row As DataGridViewRow In Me.GridRetail.Rows
                    If row.Index <> (Me.GridRetail.Rows.Count - 1) Then
                        If row.Cells(Me.DGColProducerCode.Index).Value = "" OrElse row.Cells(Me.DGColProducerName.Index).Value = "" _
                        OrElse row.Cells(Me.DGColTotalBatch.Index).Value = "0.00" OrElse _
                         row.Cells(Me.DGColGivenPrice.Index).Value > row.Cells(Me.DGColActualPrice.Index).Value OrElse _
                          row.Cells(Me.DGColGivenPrice.Index).Value = "0.00" OrElse row.Cells(Me.DGColActualPrice.Index).Value = "0.00" Then
                            row.ErrorText = "Please fill in all fields"
                            Valid = False
                        Else
                            row.ErrorText = ""
                        End If
                    End If
                Next

                If Valid = False Then
                    Me.Cursor = Cursors.Default
                    Exit Sub
                End If

                Dim MoveNo As Integer
                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction

                cmd.Connection = cnn

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Transaction = Trans

                'Get Move No.
                cmd.CommandText = "Select IsNull(Max(MoveNo),0) From Retail Where Branch=N'" & Branch & "' "
                MoveNo = CInt(cmd.ExecuteScalar) + 1

                For Each row As DataGridViewRow In Me.GridRetail.Rows
                    If row.Index <> (Me.GridRetail.Rows.Count - 1) Then
                        cmd.CommandText = "Insert into Retail (MoveNo,Branch,InsuranceCompany,BatchNo,ClientCode,ClientName,Reject,PoliciesNo,TotalBatch,Comm," & _
                                          "ActualPrice,GivenPrice,Discount,CreatedBy,Done) Values (@MoveNo,@Branch,@InsuranceCompany,@BatchNo,@ClientCode,@ClientName," & _
                                          "@Reject,@PoliciesNo,@TotalBatch,@Comm,@ActualPrice,@GivenPrice,@Discount,@CreatedBy,0)"

                        cmd.Parameters.Clear()
                        cmd.Parameters.AddWithValue("@MoveNo", MoveNo)
                        cmd.Parameters.AddWithValue("@Branch", Branch)
                        cmd.Parameters.AddWithValue("@InsuranceCompany", Me.CombInsurComp.Text.Trim)
                        cmd.Parameters.AddWithValue("@BatchNo", row.Cells(Me.DGColBatchNo.Index).Value)
                        cmd.Parameters.AddWithValue("@ClientCode", row.Cells(Me.DGColProducerCode.Index).Value)
                        cmd.Parameters.AddWithValue("@ClientName", row.Cells(Me.DGColProducerName.Index).Value)
                        cmd.Parameters.AddWithValue("@Reject", row.Cells(Me.DGColReject.Index).Value)
                        cmd.Parameters.AddWithValue("@PoliciesNo", row.Cells(Me.DGColNoOfPolicies.Index).Value)
                        cmd.Parameters.AddWithValue("@TotalBatch", row.Cells(Me.DGColTotalBatch.Index).Value)
                        cmd.Parameters.AddWithValue("@Comm", row.Cells(Me.DGColComm.Index).Value)
                        cmd.Parameters.AddWithValue("@ActualPrice", row.Cells(Me.DGColActualPrice.Index).Value)
                        cmd.Parameters.AddWithValue("@GivenPrice", row.Cells(Me.DGColGivenPrice.Index).Value)
                        cmd.Parameters.AddWithValue("@Discount", row.Cells(Me.DGColDiscount.Index).Value)
                        cmd.Parameters.AddWithValue("@CreatedBy", CurrentUser)
                        cmd.ExecuteNonQuery()
                    End If
                Next

                Trans.Commit()
                cnn.Close()

                MsgBox("Saved Successfully")

                Clear()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
            End Try
        End If
    End Sub

    Sub CalculateTotal()
        Try
            Me.Cursor = Cursors.WaitCursor

            For Each Row As DataGridViewRow In Me.GridRetail.Rows
                Row.Cells(Me.DGColTotalBatch.Index).Value = CDbl(Row.Cells(Me.DGColNoOfPolicies.Index).Value) * _
                    (CDbl(Row.Cells(Me.DGColGivenPrice.Index).Value))

                Row.Cells(Me.DGColDiscount.Index).Value = CDbl(Row.Cells(Me.DGColNoOfPolicies.Index).Value) * _
             (CDbl(Row.Cells(Me.DGColActualPrice.Index).Value) - CDbl(Row.Cells(Me.DGColGivenPrice.Index).Value))

            Next
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Sub ValidateGrid()
        Try
            Me.Cursor = Cursors.WaitCursor

            For Each row As DataGridViewRow In Me.GridRetail.Rows

                If row.Cells(Me.DGColGivenPrice.Index).Value > row.Cells(Me.DGColActualPrice.Index).Value Then
                    row.Cells(Me.DGColGivenPrice.Index).Style.BackColor = Color.Salmon
                Else
                    row.Cells(Me.DGColGivenPrice.Index).Style.BackColor = row.DefaultCellStyle.BackColor
                End If

            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Private Sub GridRetail_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridRetail.CellEndEdit

        If e.ColumnIndex = Me.DGColProducerCode.Index Then
            Try
                If Me.GridRetail.CurrentCell.Value <> "" Then
                    Me.Cursor = Cursors.WaitCursor

                    'Get Client Name
                    Dim ClientName As String
                    ClientName = GetRetailClientName(Me.GridRetail.CurrentCell.Value)

                    Me.GridRetail.CurrentRow.Cells(Me.DGColProducerName.Index).Value = ClientName

                    Me.Cursor = Cursors.Default
                Else
                    Me.GridRetail.CurrentRow.Cells(Me.DGColProducerName.Index).Value = ""
                End If
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try
        End If

        If e.ColumnIndex = Me.DGColNoOfPolicies.Index Then
            Try
                Me.GridRetail.CurrentCell.Value = Me.GridRetail.CurrentCell.Value

            Catch ex As Exception
                Me.GridRetail.CurrentCell.Value = "1"
            End Try

        ElseIf e.ColumnIndex = Me.DGColGivenPrice.Index Then
            Try
                Me.GridRetail.CurrentCell.Value = CDbl(Me.GridRetail.CurrentCell.Value).ToString("N2")
            Catch ex As Exception
                Me.GridRetail.CurrentCell.Value = "0.00"
            End Try
        ElseIf e.ColumnIndex = Me.DGColActualPrice.Index Then
            Try
                Me.GridRetail.CurrentCell.Value = CDbl(Me.GridRetail.CurrentCell.Value).ToString("N2")
            Catch ex As Exception
                Me.GridRetail.CurrentCell.Value = "0.00"
            End Try
        ElseIf e.ColumnIndex = Me.DGColReject.Index Then
            Try
                Me.GridRetail.CurrentCell.Value = Me.GridRetail.CurrentCell.Value
            Catch ex As Exception
                Me.GridRetail.CurrentCell.Value = "0"
            End Try
        ElseIf e.ColumnIndex = Me.DGColComm.Index Then
            Try
                Me.GridRetail.CurrentCell.Value = CDbl(Me.GridRetail.CurrentCell.Value).ToString("N2")
            Catch ex As Exception
                Me.GridRetail.CurrentCell.Value = "0.00"
            End Try
        End If

        CalculateTotal()
        ValidateGrid()

    End Sub
End Class